﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_ICM_Bidder : IDAL_ICM_Bidder
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();

        public D_ICM_Bidder()
        {

        }

        public IList<M_ICM_Bidder> GetFull(Nullable<int> BidId, Nullable<int> PrId
            , string collateral_return_status, string ReceiveStatus, string OfferStatus, string WinnerStatus)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_ICM_Bidder> infoList = new List<M_ICM_Bidder>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@BidId", BidId);
                dbManager.AddParameters(2, "@PrId", PrId);
                dbManager.AddParameters(3, "@collateral_return_status", collateral_return_status);
                dbManager.AddParameters(4, "@ReceiveStatus", ReceiveStatus);
                dbManager.AddParameters(5, "@OfferStatus", OfferStatus);
                dbManager.AddParameters(6, "@WinnerStatus", WinnerStatus);

                strSQL = "SELECT db.BidderId, db.ConId, db.BidId, db.PrId"
                    + ", db.ReceiveDate"
                    + ", db.ReceiptNo, db.OfferDate"
                    + ", db.BidderDate, db. DueDate, db.DueDay, db.DurTypeId"
                    + ", db.VatPercent, db.VatTypeId, db.ReceiveStatus"
                    + ", db.OfferStatus, db.WinnerStatus"
                    + ", con.NameTh"
                    + " FROM IcmBidder db"
                    + " LEFT JOIN IcmMasContact con ON bd.ConId = con.ConId"
                    + " WHERE 0 = 0"
                    + (BidId != null ? " and bd.BidId = @BidId" : "")
                    + (PrId != null ? " and bd.PrId = @PrId" : "")
                    + (ReceiveStatus != null ? " and bd.ReceiveStatus = @ReceiveStatus" : "")
                    + (OfferStatus != null ? " and bd.OfferStatus = @OfferStatus" : "")
                    + (WinnerStatus != null ? " and bd.WinnerStatus = @WinnerStatus" : "")
                    ;

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ICM_Bidder info = new M_ICM_Bidder();
                        info.icm_bidder_seq = Convert.ToInt32(drc[i]["BidderId"]);
                        info.icm_con_seq = cn.ConvertNullInt(drc[i]["ConId"]);
                        info.icm_bid_seq = cn.ConvertNullInt(drc[i]["BidId"]);
                        info.icm_pr_seq = cn.ConvertNullInt(drc[i]["PrId"]);
                        info.icm_receive_date = cn.ConvertNullDateTime(drc[i]["ReceiveDate"]);
                        info.receive_receipt_no = drc[i]["ReceiptNo"].ToString();
                        info.icm_offer_date = cn.ConvertNullDateTime(drc[i]["OfferDate"]);
                        info.icm_bidder_date = cn.ConvertNullDateTime(drc[i]["BidderId"]);
                        info.due_date = cn.ConvertNullDateTime(drc[i]["BidderId"]);
                        info.due_day = cn.ConvertNullInt(drc[i]["DueDay"]);
                        info.icm_dur_type_seq = cn.ConvertNullInt(drc[i]["BidderId"]);
                        info.vat_percent = cn.ConvertNullDouble(drc[i]["VatPercent"]);
                        info.icm_vat_seq = cn.ConvertNullInt(drc[i]["VatTypeId"]);
                        info.receive_status = drc[i]["ReceiveStatus"].ToString();
                        info.offer_status = drc[i]["OfferStatus"].ToString();
                        info.winner_status = drc[i]["BidderId"].ToString();
                        info.icm_con_name_th = drc[i]["NameTh"].ToString();

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_ICM_Bidder> GetList(Nullable<int> BidId, Nullable<int> PrId
            , string collateral_return_status, string ReceiveStatus, string OfferStatus, string WinnerStatus)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_ICM_Bidder> infoList = new List<M_ICM_Bidder>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@BidId", BidId);
                dbManager.AddParameters(2, "@PrId", PrId);
                dbManager.AddParameters(3, "@collateral_return_status", collateral_return_status);
                dbManager.AddParameters(4, "@ReceiveStatus", ReceiveStatus);
                dbManager.AddParameters(5, "@OfferStatus", OfferStatus);
                dbManager.AddParameters(6, "@WinnerStatus", WinnerStatus);

                strSQL = "SELECT bd.BidderId, con.NameTh"
                    + " FROM IcmBidder bd"
                    + " LEFT JOIN IcmMasContact con ON bd.ConId = con.ConId"
                    + " WHERE 0 = 0"
                    + (BidId != null ? " and bd.BidId = @BidId" : "")
                    + (PrId != null ? " and bd.PrId = @PrId" : "")
                    + (ReceiveStatus != null ? " and bd.ReceiveStatus = @ReceiveStatus" : "")
                    + (OfferStatus != null ? " and bd.OfferStatus = @OfferStatus" : "")
                    + (WinnerStatus != null ? " and bd.WinnerStatus = @WinnerStatus" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ICM_Bidder info = new M_ICM_Bidder();
                        info.icm_bidder_seq = Convert.ToInt32(drc[i]["BidderId"]);
                        info.icm_con_name_th = drc[i]["NameTh"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_ICM_Bidder GetInfo(Nullable<int> BidId, Nullable<int> contact_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_ICM_Bidder info = new M_ICM_Bidder();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@BidId", BidId);
                dbManager.AddParameters(2, "@contact_seq", contact_seq);

                strSQL = "SELECT bd.BidderId, bd.ConId, bd.BidId, bd.PrId, bd.ReceiveDate,"
	                    + " bd.ReceiptNo, bd.OfferDate,"
                        + " bd.BidderDate, bd.DueDate, bd.DueDay, bd.DurTypeId, bd.VatPercent, bd.VatTypeId,"
                        + " bd.ReceiveStatus, bd.OfferStatus, bd.WinnerStatus,con.NameTh,"
                        + " bid.Name as BidName"
                        + " FROM IcmBidder bd"
                        + " LEFT JOIN IcmBid bid ON bd.BidId = bid.BidId"
                        + " LEFT JOIN IcmMasContact con ON bd.ConId = con.ConId"
                        + " WHERE 0 = 0"
                        + (BidId != null ? " AND bd.BidId = @BidId" : "")
                        + (contact_seq != null ? " AND con.ConId = @contact_seq" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)

                {
                    info.icm_bidder_seq = Convert.ToInt32(drc[0]["BidderId"]);
                    info.icm_con_seq = cn.ConvertNullInt(drc[0]["ConId"]);
                    info.icm_bid_seq = cn.ConvertNullInt(drc[0]["BidId"]);
                    info.icm_pr_seq = cn.ConvertNullInt(drc[0]["PrId"]);
                    info.icm_receive_date = cn.ConvertNullDateTime(drc[0]["ReceiveDate"]);
                    info.receive_receipt_no = drc[0]["ReceiptNo"].ToString();
                    info.icm_offer_date = cn.ConvertNullDateTime(drc[0]["OfferDate"]);
                    info.icm_bidder_date = cn.ConvertNullDateTime(drc[0]["BidderDate"]);
                    info.due_date = cn.ConvertNullDateTime(drc[0]["DueDate"]);
                    info.due_day = cn.ConvertNullInt(drc[0]["DueDay"]);
                    info.icm_dur_type_seq = cn.ConvertNullInt(drc[0]["DurTypeId"]);
                    info.vat_percent = cn.ConvertNullDouble(drc[0]["VatPercent"]);
                    info.icm_vat_seq = cn.ConvertNullInt(drc[0]["VatTypeId"]);
                    info.receive_status = drc[0]["ReceiveStatus"].ToString();
                    info.offer_status = drc[0]["OfferStatus"].ToString();
                    info.winner_status = drc[0]["WinnerStatus"].ToString();
                    info.icm_con_name_th = drc[0]["NameTh"].ToString();
                    info.icm_bid_name = drc[0]["BidName"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public M_ICM_Bidder GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_ICM_Bidder info = new M_ICM_Bidder();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT db.BidderId, db.ConId, db.BidId, db.PrId"
                    + ", db.ReceiveDate "
                    + ", db.ReceiptNo, db.OfferDate "
                    + ", db.BidderDate, db.DueDate, db.DueDay, db.DurTypeId"
                    + ", db.VatPercent, db.VatTypeId, db.ReceiveStatus"
                    + ", db.OfferStatus, db.WinnerStatus"
                    + ", con.NameTh, bid.Name as BidName"
                    + " FROM IcmBidder db"
                    + " LEFT JOIN IcmBid bid ON db.BidId = bid.BidId"
                    + " LEFT JOIN IcmMasContact con ON db.ConId = con.ConId"
                    + " WHERE db.BidderId = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.icm_bidder_seq = Convert.ToInt32(drc[0]["BidderId"]);
                    info.icm_con_seq = cn.ConvertNullInt(drc[0]["ConId"]);
                    info.icm_bid_seq = cn.ConvertNullInt(drc[0]["BidId"]);
                    info.icm_pr_seq = cn.ConvertNullInt(drc[0]["PrId"]);
                    info.icm_receive_date = cn.ConvertNullDateTime(drc[0]["ReceiveDate"]);
                    info.receive_receipt_no = drc[0]["ReceiptNo"].ToString();
                    info.icm_offer_date = cn.ConvertNullDateTime(drc[0]["OfferDate"]);
                    info.icm_bidder_date = cn.ConvertNullDateTime(drc[0]["BidderDate"]);
                    info.due_date = cn.ConvertNullDateTime(drc[0]["DueDate"]);
                    info.due_day = cn.ConvertNullInt(drc[0]["DueDay"]);
                    info.icm_dur_type_seq = cn.ConvertNullInt(drc[0]["DurTypeId"]);
                    info.vat_percent = cn.ConvertNullDouble(drc[0]["VatPercent"]);
                    info.icm_vat_seq = cn.ConvertNullInt(drc[0]["VatTypeId"]);
                    info.receive_status = drc[0]["ReceiveStatus"].ToString();
                    info.offer_status = drc[0]["OfferStatus"].ToString();
                    info.winner_status = drc[0]["WinnerStatus"].ToString();
                    info.icm_con_name_th = drc[0]["NameTh"].ToString();
                    info.icm_bid_name = drc[0]["BidName"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public bool CheckExist(Nullable<int> BidId, Nullable<int> contact_seq)
        {
            bool bFound = false;



            return bFound;
        }

        public string Insert(M_ICM_Bidder info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(23);

                dbManager.AddParameters(0, "@BidderId", info.icm_bidder_seq);
                dbManager.AddParameters(1, "@ConId", info.icm_con_seq);
                dbManager.AddParameters(2, "@BidId", info.icm_bid_seq);
                dbManager.AddParameters(3, "@PrId", info.icm_pr_seq);
                dbManager.AddParameters(4, "@icm_bidder_tran_seq", info.icm_bidder_tran_seq);
                dbManager.AddParameters(5, "@ReceiveDate", info.icm_receive_date);
                dbManager.AddParameters(6, "@icm_receive_time", "");
                dbManager.AddParameters(7, "@ReceiptNo", info.receive_receipt_no);
                dbManager.AddParameters(8, "@OfferDate", info.icm_offer_date);
                dbManager.AddParameters(9, "@icm_offer_time", info.icm_offer_time);
                dbManager.AddParameters(10, "@collateral_return_date", info.collateral_return_date);
                dbManager.AddParameters(11, "@collateral_return_status", info.collateral_return_status);
                dbManager.AddParameters(12, "@BidderDate", info.icm_bidder_date);
                dbManager.AddParameters(13, "@DueDate", info.due_date);
                dbManager.AddParameters(14, "@DueDay", info.due_day);
                dbManager.AddParameters(15, "@DurTypeId", info.due_day);
                dbManager.AddParameters(16, "@VatPercent", info.vat_percent);
                dbManager.AddParameters(17, "@VatTypeId", info.icm_vat_seq);
                dbManager.AddParameters(18, "@ReceiveStatus", info.receive_status);
                dbManager.AddParameters(19, "@OfferStatus", info.offer_status);
                dbManager.AddParameters(20, "@WinnerStatus", info.winner_status);
                dbManager.AddParameters(21, "@created_by", info.created_by);
                dbManager.AddParameters(22, "@created_when", info.created_when);

                strSQL = "INSERT INTO IcmBidder"
                    + " (ConId, BidId, PrId"
                    + ", ReceiveDate, ReceiptNo"
                    + ", ReceiveStatus"
                    + ", OfferStatus"
                    + ", CreatedBy "
                    + ", CreatedWhen)"
                    + " VALUES(@ConId"
                    + ", @BidId"
                    + ", (SELECT PrId FROM IcmBid WHERE BidId = @BidId)"
                    + ", @ReceiveDate"
                    + ", @ReceiptNo"
                    + ", @ReceiveStatus"
                    + ", @OfferStatus"
                    + ", @created_by"
                    + ", @created_when"
                    + " );"
                    + " select scope_identity();";
                var strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();
                return strSeq;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateReceive(M_ICM_Bidder info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(23);

                dbManager.AddParameters(0, "@BidderId", info.icm_bidder_seq);
                dbManager.AddParameters(1, "@ConId", info.icm_con_seq);
                dbManager.AddParameters(2, "@BidId", info.icm_bid_seq);
                dbManager.AddParameters(3, "@PrId", info.icm_pr_seq);
                dbManager.AddParameters(4, "@icm_bidder_tran_seq", info.icm_bidder_tran_seq);
                dbManager.AddParameters(5, "@ReceiveDate", info.icm_receive_date);
                dbManager.AddParameters(6, "@icm_receive_time", info.icm_receive_time);
                dbManager.AddParameters(7, "@ReceiptNo", info.receive_receipt_no);
                dbManager.AddParameters(8, "@OfferDate", info.icm_offer_date);
                dbManager.AddParameters(9, "@icm_offer_time", info.icm_offer_time);
                dbManager.AddParameters(10, "@collateral_return_date", info.collateral_return_date);
                dbManager.AddParameters(11, "@collateral_return_status", info.collateral_return_status);
                dbManager.AddParameters(12, "@BidderDate", info.icm_bidder_date);
                dbManager.AddParameters(13, "@DueDate", info.due_date);
                dbManager.AddParameters(14, "@DueDay", info.due_day);
                dbManager.AddParameters(15, "@DurTypeId", info.icm_dur_type_seq);
                dbManager.AddParameters(16, "@VatPercent", info.vat_percent);
                dbManager.AddParameters(17, "@VatTypeId", info.icm_vat_seq);
                dbManager.AddParameters(18, "@ReceiveStatus", info.receive_status);
                dbManager.AddParameters(19, "@OfferStatus", info.offer_status);
                dbManager.AddParameters(20, "@WinnerStatus", info.winner_status);
                dbManager.AddParameters(21, "@UpdatedBy", info.updated_by);
                dbManager.AddParameters(22, "@UpdatedWhen", info.updated_when);

                strSQL = "UPDATE IcmBidder"
                    + " set ConId = @ConId"
                    + ", BidId = @BidId"
                    + ", PrId = @PrId"
                    + ", ReceiveDate = @ReceiveDate"
                    + ", ReceiptNo = @ReceiptNo"
                    + ", ReceiveStatus = @ReceiveStatus"
                    + ", UpdatedBy = @UpdatedBy"
                    + ", UpdatedWhen = @UpdatedWhen"
                    + " WHERE BidderId = @BidderId";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string UpdateOffer(M_ICM_Bidder info, string receipt_no, Nullable<double> amount)
        {
            string strSQL;
            //string strResult = "";
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(25);

                dbManager.AddParameters(0, "@BidderId", info.icm_bidder_seq);
                dbManager.AddParameters(1, "@ConId", info.icm_con_seq);
                dbManager.AddParameters(2, "@BidId", info.icm_bid_seq);
                dbManager.AddParameters(3, "@PrId", info.icm_pr_seq);
                dbManager.AddParameters(4, "@icm_bidder_tran_seq", info.icm_bidder_tran_seq);
                dbManager.AddParameters(5, "@ReceiveDate", info.icm_receive_date);
                dbManager.AddParameters(6, "@icm_receive_time", info.icm_receive_time);
                dbManager.AddParameters(7, "@ReceiptNo", info.receive_receipt_no);
                dbManager.AddParameters(8, "@OfferDate", info.icm_offer_date);
                dbManager.AddParameters(9, "@icm_offer_time", info.icm_offer_time);
                dbManager.AddParameters(10, "@collateral_return_date", info.collateral_return_date);
                dbManager.AddParameters(11, "@collateral_return_status", info.collateral_return_status);
                dbManager.AddParameters(12, "@BidderDate", info.icm_bidder_date);
                dbManager.AddParameters(13, "@DueDate", info.due_date);
                dbManager.AddParameters(14, "@DueDay", info.due_day);
                dbManager.AddParameters(15, "@DurTypeId", info.icm_dur_type_seq);
                dbManager.AddParameters(16, "@VatPercent", info.vat_percent);
                dbManager.AddParameters(17, "@VatTypeId", info.icm_vat_seq);
                dbManager.AddParameters(18, "@ReceiveStatus", info.receive_status);
                dbManager.AddParameters(19, "@OfferStatus", info.offer_status);
                dbManager.AddParameters(20, "@WinnerStatus", info.winner_status);

                dbManager.AddParameters(21, "@UpdatedBy", info.updated_by);
                dbManager.AddParameters(22, "@UpdatedWhen", info.updated_when);

                dbManager.AddParameters(23, "@receipt_no", receipt_no);
                dbManager.AddParameters(24, "@amount", amount);

                strSQL = "UPDATE IcmBidder"
                    + " SET "
                    + " OfferDate = @OfferDate"
                    + ", OfferStatus = @OfferStatus"
                    + ", UpdatedBy = @UpdatedBy"
                    + ", UpdatedWhen = @UpdatedWhen"
                    + " WHERE BidderId = @BidderId";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                strSQL = "INSERT IcmBidderCollateral"
                    + " (ColTypeId, Amount, BidderId, ReceiptNo"
                    + " ,ColStatusId, ColCategoryId"
                    + ", CreatedBy, CreatedWhen)"
                    + " VALUES(1, @amount, @BidderId, @receipt_no"
                    + " ,31 , 30" //Hard code by Mai jing jing NA
                    + ", @UpdatedBy, @UpdatedWhen);"
                    + " select scope_identity();";
                var strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();
                return strSeq;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int seq)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "DELETE FROM IcmBidder"
                    + " WHERE BidderId = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string AmountBidder(string BidId)
        {
            string strSQL;
            string AmountBidder = "";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@BidId", BidId);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT b.DocumentAmount FROM IcmBid b"
                        +" WHERE b.BidId = @BidId";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                AmountBidder = Convert.ToString(dtb.Rows.Count > 0 ? dtb.Rows[0][0] : "0");
                return AmountBidder;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }

        }

    }
}
